/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package stateless;

import java.sql.*;
    import java.util.ArrayList;
import javax.ejb.Stateless;

/**
 *
 * @author stan
 */
@Stateless
public class FoodMateEJB implements FoodMateEJBRemote {

    @Override
    public String Hello() {
        return "Hello World";
    }

    @Override
    public String test() 
    {
        String out = "";
        
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed()) {
                out += "資料庫連線測試成功！";
                con.close();
            }
        }
        catch(ClassNotFoundException e) {
            out += "找不到驅動程式類別";
            out += e.toString();
        }
        catch(SQLException e) {
            out += e.toString();
        }
        return out;
    }


    @Override
    public ArrayList<Restaurant> FindRestaurant(String style, String loc, String name)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<Restaurant> rstlist = new ArrayList<Restaurant>();
                PreparedStatement sql = con.prepareStatement(
                        "select * from restaurant where style = ? and location = ? and name like ?");
                sql.setString(1, style);
                sql.setString(2, loc);
                sql.setString(3, "%" + name + "%");

                ResultSet result = sql.executeQuery();
                
                while(result.next())
                {
                    Restaurant rst = new Restaurant();
                    rst.Id = result.getInt("id");
                    rst.Name = result.getString("name");
                    rst.Style = result.getString("style");
                    rst.Location = result.getString("location");
                    rst.Email = result.getString("email");
                    rst.Picture = result.getString("picture");
                    rst.Description = result.getString("description");

                    rstlist.add(rst);
                }
                
                result.close();
                con.close();
                return rstlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public Restaurant FindRestaurant(int id)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "select * from restaurant where id = ?");
                sql.setInt(1, id);

                ResultSet result = sql.executeQuery();

                Restaurant rst = new Restaurant();
                if(result.next())
                {
                    rst.Id = result.getInt("id");
                    rst.Name = result.getString("name");
                    rst.Style = result.getString("style");
                    rst.Location = result.getString("location");
                    rst.Email = result.getString("email");
                    rst.Picture = result.getString("picture");
                    rst.Description = result.getString("description");
                }

                result.close();
                con.close();
                return rst;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public boolean AddRestaurant(Restaurant rst) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "insert into restaurant (name, style, location, email, picture, description)" +
                        "value (?, ?, ?, ?, ?, ?)");
                sql.setString(1, rst.Name);
                sql.setString(2, rst.Style);
                sql.setString(3, rst.Location);
                sql.setString(4, rst.Email);
                sql.setString(5, rst.Picture);
                sql.setString(6, rst.Description);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean UpdateRestaurant(Restaurant rst) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "update restaurant set "
                        + " name = ?, style = ?, location = ?, email = ?, picture = ?, description = ?"
                        + " where id = ?");
                sql.setString(1, rst.Name);
                sql.setString(2, rst.Style);
                sql.setString(3, rst.Location);
                sql.setString(4, rst.Email);
                sql.setString(5, rst.Picture);
                sql.setString(6, rst.Description);
                sql.setInt(7, rst.Id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean DeleteRestaurant(int id) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "delete from restaurant where id = ?");
                sql.setInt(1, id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public ReserveInfo FindReserveInfo(int id)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement("select * from reserveinfo where id = ?");
                sql.setInt(1, id);

                ResultSet result = sql.executeQuery();

                ReserveInfo rsv = new ReserveInfo();
                if(result.next())
                {                    
                    rsv.Id = result.getInt("id");
                    rsv.UserId = result.getInt("userid");
                    rsv.RestaurantId = result.getInt("restaurantid");
                    rsv.Date = result.getDate("date");
                    rsv.Time = result.getInt("time");
                    rsv.Memo = result.getString("memo");
                    rsv.Host = result.getInt("host");
                    rsv.Status = result.getInt("status");
                }

                result.close();
                con.close();
                return rsv;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }


    @Override
    public ArrayList<ReserveInfo> FindReserveInfoByRestaurant(int rstid)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<ReserveInfo> rsvlist = new ArrayList<ReserveInfo>();
                PreparedStatement sql = con.prepareStatement("select * from reserveinfo where restaurantid = ?");
                sql.setInt(1, rstid);

                ResultSet result = sql.executeQuery();

                while(result.next())
                {
                    ReserveInfo rsv = new ReserveInfo();
                    rsv.Id = result.getInt("id");
                    rsv.UserId = result.getInt("userid");
                    rsv.RestaurantId = result.getInt("restaurantid");
                    rsv.Date = result.getDate("date");
                    rsv.Time = result.getInt("time");
                    rsv.Memo = result.getString("memo");
                    rsv.Host = result.getInt("host");
                    rsv.Status = result.getInt("status");

                    rsvlist.add(rsv);
                }

                result.close();
                con.close();
                return rsvlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public ArrayList<ReserveInfo> FindReserveInfoByHost(int hostid)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<ReserveInfo> rsvlist = new ArrayList<ReserveInfo>();
                PreparedStatement sql = con.prepareStatement("select * from reserveinfo where host = ?");
                sql.setInt(1, hostid);

                ResultSet result = sql.executeQuery();

                while(result.next())
                {
                    ReserveInfo rsv = new ReserveInfo();
                    rsv.Id = result.getInt("id");
                    rsv.UserId = result.getInt("userid");
                    rsv.RestaurantId = result.getInt("restaurantid");
                    rsv.Date = result.getDate("date");
                    rsv.Time = result.getInt("time");
                    rsv.Memo = result.getString("memo");
                    rsv.Host = result.getInt("host");
                    rsv.Status = result.getInt("status");

                    rsvlist.add(rsv);
                }

                result.close();
                con.close();
                return rsvlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public ArrayList<ReserveInfo> FindReserveInfoByUser(int usrid)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<ReserveInfo> rsvlist = new ArrayList<ReserveInfo>();
                PreparedStatement sql = con.prepareStatement("select * from reserveinfo where userid = ?");
                sql.setInt(1, usrid);

                ResultSet result = sql.executeQuery();

                while(result.next())
                {
                    ReserveInfo rsv = new ReserveInfo();
                    rsv.Id = result.getInt("id");
                    rsv.UserId = result.getInt("userid");
                    rsv.RestaurantId = result.getInt("restaurantid");
                    rsv.Date = result.getDate("date");
                    rsv.Time = result.getInt("time");
                    rsv.Memo = result.getString("memo");
                    rsv.Host = result.getInt("host");
                    rsv.Status = result.getInt("status");

                    rsvlist.add(rsv);
                }

                result.close();
                con.close();
                return rsvlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public ArrayList<ReserveInfo> FindReserveInfoByRestaurantAndTime(int rstid, Date dat, int time)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<ReserveInfo> rsvlist = new ArrayList<ReserveInfo>();
                PreparedStatement sql = con.prepareStatement(
                        "select * from reserveinfo where restaurantid = ? and date = ? and time = ?");
                sql.setInt(1, rstid);
                sql.setDate(2, dat);
                sql.setInt(3, time);

                ResultSet result = sql.executeQuery();

                while(result.next())
                {
                    ReserveInfo rsv = new ReserveInfo();
                    rsv.Id = result.getInt("id");
                    rsv.UserId = result.getInt("userid");
                    rsv.RestaurantId = result.getInt("restaurantid");
                    rsv.Date = result.getDate("date");
                    rsv.Time = result.getInt("time");
                    rsv.Memo = result.getString("memo");
                    rsv.Host = result.getInt("host");
                    rsv.Status = result.getInt("status");

                    rsvlist.add(rsv);
                }

                result.close();
                con.close();
                return rsvlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public boolean AddReserveInfo(ReserveInfo rsv)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");

            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement("select * from reserveinfo where userid = ? and date=? and time=? and restaurantid=?");
                sql.setInt(1, rsv.UserId);
                sql.setDate(2, rsv.Date);
                sql.setInt(3, rsv.Time);
                sql.setInt(4, rsv.RestaurantId);

                ResultSet result = sql.executeQuery();

                if(result.next())
                {
                    return false;
                }
            }


            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "insert into reserveinfo (userid, restaurantid, date, time, memo, host, status)" +
                        "value (?, ?, ?, ?, ?, ?, ?)");
                sql.setInt(1, rsv.UserId);
                sql.setInt(2, rsv.RestaurantId);
                sql.setDate(3, rsv.Date);
                sql.setInt(4, rsv.Time);
                sql.setString(5, rsv.Memo);
                sql.setInt(6, rsv.Host);
                sql.setInt(7,rsv.Status);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean UpdateReserveInfo(ReserveInfo rsv)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "update reserveinfo set"
                        + " userid = ?, restaurantid = ?, date = ?, time = ?, memo = ?, host = ?, status = ?"
                        + " where id = ?");
                sql.setInt(1, rsv.UserId);
                sql.setInt(2, rsv.RestaurantId);
                sql.setDate(3, rsv.Date);
                sql.setInt(4, rsv.Time);
                sql.setString(5, rsv.Memo);
                sql.setInt(6, rsv.Host);
                sql.setInt(7,rsv.Status);
                sql.setInt(8,rsv.Id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean DeleteReserveInfo(int id)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "delete from reserveinfo where id = ?");
                sql.setInt(1, id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }



    @Override
    public User FindUser(int id)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "select * from user where id = ?");
                sql.setInt(1, id);

                ResultSet result = sql.executeQuery();

                User usr = new User();

                if(result.next())
                {
                    
                    usr.Id = result.getInt("id");
                    usr.Name = result.getString("name");
                    usr.Password = result.getString("password");
                    usr.Gender = result.getBoolean("gender");
                    usr.Email = result.getString("email");
                    usr.Picture = result.getString("picture");
                    usr.Description = result.getString("description");
                    usr.Baned = result.getBoolean("baned");
                }

                result.close();
                con.close();
                return usr;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public boolean AddUser(User usr) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "insert into user (id, password, name, gender, email, picture, description, baned)" +
                        "value (?, ?, ?, ?, ?, ?, ?, ?)");
                sql.setInt(1, usr.Id);
                sql.setString(2, usr.Password);
                sql.setString(3, usr.Name);
                sql.setBoolean(4, usr.Gender);
                sql.setString(5, usr.Email);
                sql.setString(6, usr.Picture);
                sql.setString(7, usr.Description);
                sql.setBoolean(8, usr.Baned);
        
                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean UpdateUser(User usr) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "update user set "
                        + " passowrd = ?, name = ?, gender = ?, email = ?, picture = ?, description = ?, baned = ?"
                        + " where id = ?");
                sql.setString(1, usr.Password);
                sql.setString(2, usr.Name);
                sql.setBoolean(3, usr.Gender);
                sql.setString(4, usr.Email);
                sql.setString(5, usr.Picture);
                sql.setString(6, usr.Description);
                sql.setBoolean(7, usr.Baned);
                sql.setInt(8, usr.Id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean DeleteUser(int id) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "delete from user where id = ?");
                sql.setInt(1, id);

                int flag = sql.executeUpdate();

                con.close();
                return flag > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    /* For Admin Page */
    @Override
    public ArrayList<User> ListAllUser() {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                ArrayList<User> userlist = new ArrayList<User>();
                PreparedStatement sql = con.prepareStatement(
                        "select * from User");

                ResultSet result = sql.executeQuery();

                while(result.next())
                {
                    User userInfo = new User();
                    userInfo.Id = result.getInt("id");
                    userInfo.Picture = result.getString("picture");
                    userInfo.Baned = result.getBoolean("baned");

                    userlist.add(userInfo);
                }

                result.close();
                con.close();
                return userlist;
            }
            else
            {
                return null;
            }
        }
        catch(ClassNotFoundException e) {
            return null;
        }
        catch(SQLException e) {
            return null;
        }
    }

    @Override
    public boolean BanUser(int id) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "update user set "
                        + "baned = true" + " where id = ?");
                sql.setInt(1, id);

                int result = sql.executeUpdate();

                con.close();
                return result > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    @Override
    public boolean UnbanUser(int id) {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/FoodMate","root", "2xdialxl");
            if(con != null && !con.isClosed())
            {
                PreparedStatement sql = con.prepareStatement(
                        "update user set "
                        + "baned=false" + " where id=?");
                sql.setInt(1, id);

                int result = sql.executeUpdate();

                con.close();
                return result > 0;
            }
            else
            {
                return false;
            }
        }
        catch(ClassNotFoundException e) {
            return false;
        }
        catch(SQLException e) {
            return false;
        }
    }

    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method") 
}
